package com.tim.dao.Impl;

import com.tim.dao.CarStallDao;
import com.tim.pojo.CarStall;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/29 11:56
 */
public class CarStallDaoImpl implements CarStallDao {
    @Override
    public int deleteCarStall(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from carstall where id = ?";
        Object[] args ={id};
        return qr.update(sql,args);
    }

    @Override
    public int addCarStall(CarStall carStall) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into carstall (id,carPlot,stallNum,stallName,creatorTime,status) values (?,?,?,?,?,?)";
        Object[] args = {carStall.getId(),carStall.getCarPlot(),carStall.getStallNum(),carStall.getStallName(),carStall.getCreatorTime(),carStall.getStatus()};
        return qr.update(sql,args);
    }

    @Override
    public CarStall selectOne(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from carstall where id = ?";
        Object[] args = {id};
        return qr.query(sql,new BeanHandler<CarStall>(CarStall.class),args);
    }

    @Override
    public int updateCarStall(CarStall carStall) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql ="update carstall set carPlot=?,stallNum=?,stallName=?,creatorTime=?,status=? where id =?";
        Object[] args = {carStall.getCarPlot(),carStall.getStallNum(),carStall.getStallName(),carStall.getCreatorTime(),carStall.getStatus(),carStall.getId()};
        return  qr.update(sql,args);
    }

    @Override
    public List<CarStall> searchCarStall(String carPlot) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from carstall where carPlot like ?";
        Object[] args = {"%"+carPlot+"%"};
        return qr.query(sql,new BeanListHandler<CarStall>(CarStall.class),args);
    }

    @Override
    public List<CarStall> limit(Integer currentPage, Integer pageSize, String carPlot) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(carPlot==null){
            String sql ="select * from carstall limit ?,?";
            Object[] args = {(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<CarStall>(CarStall.class),args);
        }else{
            String sql ="select * from carstall where carPlot = ? limit ?,?";
            Object[] args = {"%"+carPlot+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<CarStall>(CarStall.class),args);
        }
    }

    @Override
    public int count(String carPlot) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(carPlot == null){
            String sql = "select count(*) from carstall";
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from carstall where carPlot like ?";
            return qr.query(sql,new ScalarHandler<Number>(),"%"+carPlot+"%").intValue();
        }
    }
}
